package com.hs.spring.jdbc.dao;

import com.hs.spring.jdbc.pojo.User;
import com.hs.spring.jdbc.util.JdbcUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository("userDao")
public class UserDao {

    @Autowired
    private DataSource dataSource;

    public List<User> findAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        try{
            conn = dataSource.getConnection();
            stmt = conn.createStatement();

            String querySql = "select * from user";
            rs = stmt.executeQuery(querySql);
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                //添加到集合中去
                userList.add(user);
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            JdbcUtil.close(conn, stmt, rs);
        }
        return userList;
    }

    public User getById(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        User user = null;
        try{
            String querySql = "select * from user where id=?";
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(querySql);
            //设置参数
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            //获取结果
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            JdbcUtil.close(conn, stmt, rs);
        }
        return user;
    }

    public int delete(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        int result = 0;
        try{
            String updateSql = "delete from user where id=?";
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(updateSql);
            //设置参数
            stmt.setInt(1, id);
            //执行更新
            result = stmt.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            JdbcUtil.close(conn, stmt, null);
        }
        return result;
    }

    public int update(User user){
        Connection conn = null;
        PreparedStatement stmt = null;
        int result = 0;
        try{
            String updateSql = "update user set username=? and password=? where id=?";
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(updateSql);
            //设置参数
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setInt(3, user.getId());
            //执行更新
            result = stmt.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            JdbcUtil.close(conn, stmt, null);
        }
        return result;
    }

    public int updateByBatch(List<User> userList){
        Connection conn = null;
        PreparedStatement stmt = null;
        int result = 0;
        try{
            String updateSql = "update user set username=? and password=? where id=?";
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(updateSql);

            for(User user : userList){
                //设置参数
                stmt.setString(1, user.getUsername());
                stmt.setString(2, user.getPassword());
                stmt.setInt(3, user.getId());

                stmt.addBatch();
            }
            //执行更新
            result = stmt.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            JdbcUtil.close(conn, stmt, null);
        }
        return result;
    }

    public int updateWithTransaction(User user, Connection conn) throws Exception{
        PreparedStatement stmt = null;
        int result = 0;
        try{
            String updateSql = "update user set username=? and password=? where id=?";
            stmt = conn.prepareStatement(updateSql);
            //设置参数
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setInt(3, user.getId());

            if(user.getMoney() < 0){
                throw new Exception("金额不能是负数");
            }

            //执行更新
            result = stmt.executeUpdate();
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }
}
